Converting from other Databases to PostgreSQL

From PostgreSQL wiki
Jump to navigationJump to search

Terminology

Non-specific

  • Ispirer Toolkit performs automatic migration of any popular RDBMS to PostgreSQL. A free demo of Ispirer Toolkit is available.
  • Convertum.ru is a robust tool for automatic migration of more than 25 databases, including migration to PostgreSQL. 30-days trial is available for free.
  • DBConvert Streams is a versatile software that supports the conversion of various databases, including PostgreSQL, and enables real-time Change Data Capture (CDC) data replication. It provides cloud data integration and streaming capabilities across distributed platforms.
  • Omni Loader is a massively parallel and horizontally scalable enterprise-class database migration solution. Easily loads millions of records per second into PostgreSQL.
  • AbstraLinx is a metadata management GUI tool for Salesforce with the ability to convert Salesforce schema to PostgreSQL and other databases, such as MySQL, Oracle, SQL Server, Firebird, SQLite, etc.
  • Full Convert is a reliable and easy-to-use database migration and synchronization solution, converting from more than 40 database engines to PostgreSQL and back.
  • pgloader knows how to load data from MySQL, SQLite, MS SQL Server, dBase files, CSV files and fixed-width data files, and more. Released under The PostgreSQL Licence.
  • DBConvert PostgreSQL database migration and sync software Database conversion and synchronization between PostgreSQL/ Amazon RDS, MySQL, MS SQL Server, SQL Azure, Oracle, MS Access. PostgreSQL supported up to 13, so not completely up-to-date.
  • dbForge Studio for PostgreSQL is a feature-rich IDE for database development and management, whose features include comparison and synchronization of database schemas and table data, as well as migration of data from/to various relational databases such as Oracle, MySQL, Microsoft SQL Server, and MariaDB.
  • dbForge Data Compare for PostgreSQL is a tool that helps locate and review table data differences in PostgreSQL databases, create detailed comparison reports, and synchronize the content of source and target databases.
  • dbForge Schema Compare for PostgreSQL/Redshift is a free solution that helps find and analyze differences in database schemas, generate comprehensive comparison reports, and synchronize schema changes in source and target databases.
  • ERBuilder is a Windows GUI tool that helps to convert from various DBMS schema to PostgreSQL. Supported databases: MySQL, Oracle, SQL Server, Firebird, SQLite, Amazon Redshift, Amazon RDS, Microsoft Azure SQL database.
  • ESF Database Migration Toolkit Enables you to transfer data across various databases, supporting PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, Informix, Microsoft Access, Microsoft Excel, dBase, Foxpro, Firbird, SQLite etc. - by Martin Williams
  • Comparison of different SQL implementations by Troels Arvin (covers PG 8.4 and MySQL 5.0, so somewhat outdated)
  • Transactional DDL in PostgreSQL: A Competitive Analysis by Greg Smith
  • dataPro Conversion and database objects transfer tool for PostgreSQL, SQLite, MySQL, Oracle, SQL Server and Microsoft Access. convert tables schema and migrate data from one database type to another. Covers PG7 to PG9, so quite outdated.
  • DataDiff CrossDB is a Windows GUI utility to compare and synchronize/transfer data from PostgreSQL to/from Oracle, MSSQL, MS Access or MySQL databases.
  • PostgreSQL Data Wizard is a Windows GUI utility to transfer both schema and data from any ADO-compatible source (like MS Access, MySQL, SQL Server, Oracle, etc) to PostgreSQL.
  • SQL::Translator is a Perl module for translating table definitions between different software.
  • Foreign data wrappers may be useful for exporting data from other databases
  • Postgres Migration Toolkit Software pack to convert Oracle, MySQL, SQL Server and FoxPro to PostgreSQL, and vice versa.
  • sqlacrossover SQLAlchemy-based cross-database migration tool
  • Skyvia Web service for cloud data integration for PostgreSQL with Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, QuickBooks, FreshBooks, ExactTarget, MailChimp, Bigcommerce, MySQL, SQL Server, SQL Azure, Amazon RDS.
  • OmniDB Open source full-featured web tool for database management. Currently supports PostgreSQL only. More RDBMS support coming soon, including the ability of converting databases from any supported RDBMS to PostgreSQL and back.
  • Exportizer Enterprise Universal data conversion tool for PostgreSQL, Oracle, SQL Server, SQLite, Interbase, Firebird, MySQL, DB2, Informix, SQL Anywhere, Microsoft Access, Microsoft Excel, dBase, CSV. Can export data either from GUI or from command line.
  • jOOQ SQL Translator Web service for SQL syntax translation between > 25 RDBMS dialects

Apache Derby

  • derby2pg Converter program that will convert the tables, data and indexes in a given schema to a script that can be used to populate a PostgreSQL database.

DBase II, III, IV+ / DBF Format

FileMaker Pro

IBM DB2

  • Ispirer Toolkit automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from DB2 LUW, DB2 AS/400 (iSeries) and DB2 OS/390 (z/OS) to PostgreSQL. A free demo of Ispirer Toolkit is available.
  • Convertum.ru is a robust tool for automatic migration of more than 25 databases, including migration from DB2 LUW, DB2 iSeries, and DB2 z/OS to PostgreSQL. 30-days trial is available for free.
  • Omni Loader is a massively parallel and horizontally scalable enterprise-class database migration solution. Easily loads millions of records per second from DB2 LUW, iSeries, z/OS into PostgreSQL on-prem or on the cloud.
  • Migrating from DB2 to PostgreSQL
  • Full Convert is a reliable and easy-to-use database migration and synchronization solution, converting from more than 40 database engines to PostgreSQL and back (including DB2 LUW, DB2 iSeries, and DB2 z/OS).
  • db2topg Migration tool to convert a DB2 UDB Database into a PostgreSQL database.
  • Exportizer Enterprise Converts data from DB2 to PostgreSQL and back.
  • ESF Database Migration Toolkit How to migrate DB2 to PostgreSQL in an easy way.

InterBase

  • Ispirer Toolkit automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from InterBase as well as Firebird to PostgreSQL. A free demo of Ispirer Toolkit is available.
  • Convertum.ru is a robust tool for automatic migration of more than 25 databases, including migration from Interbase to PostgreSQL. 30-days trial is available for free.
  • DBConvert migration software that exports data from Interbase/ Firebird to PostgreSQL. DBSync tool enables synchronization between Interbase and Postgres.
  • DBReplicate - Simplify Interbase->PostgreSQL conversions by Kirk Strauser
  • Full Convert is a reliable and easy-to-use database migration and synchronization solution, converting from more than 40 database engines to PostgreSQL and back (including Interbase).
  • Exportizer Enterprise Converts data from Interbase to PostgreSQL and back.
  • ESF Database Migration Toolkit How to migrate Interbase/FireBird to PostgreSQL in an easy way.

Microsoft Access

  • ESF Database Migration Toolkit How to migrate MS Access to PostgreSQL in an easy way.
  • Cross-Database conversion and synchronization software Transfer data from Microsoft Access to PostgreSQL and keep them synchronized.
  • Linking to MS Access tables with ogr_fdw If your PostgreSQL is on windows, you can use ogr_fdw foreign data wrapper, packaged with PostGIS 2.2+ bundle for windows via application stackbuilder. With PostgresQL 9.5, you can use IMPORT FOREIGN SCHEMA to link in all the MS access tables and then cherry pick how you want to restructure.
  • exportSQL - a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL by Dobrica Pavlinusic. Based on the work of Pedro Freire
  • Full Convert is a reliable and easy-to-use database migration and synchronization solution, converting from more than 40 database engines to PostgreSQL and back (including Access).
  • Exportizer Enterprise Converts data from MS Access to PostgreSQL and back.
  • MDB Tools by Brian Bruns
    • A quick way to dump all tables as tsv or csv files
for TT in $(mdb-tables file.mdb); do
     mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.tsv"
done

for TT in $(mdb-tables file.mdb); do
     mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"
done

If the tablenames have embedded spaces... 

mdb-tables -1 file.mdb| while read TT
do
     mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"
done

A shell script that may be useful for converting entire databases: 

#!/bin/sh -e

mdbfn=$1
schemafn=$2
fkfn=$3
datafn=$4
schema=$5

tf=$(tempfile)

pre=""
[ -n "${schema}" ] && pre="\"${schema}\"."

mdb-schema "${mdbfn}" postgres > "${tf}"

# Schema file
echo "BEGIN;\n" > "${schemafn}"

sp=""
[ -n "${schema}" ] && echo "CREATE SCHEMA \"${schema}\";\n" >> "${schemafn}"
[ -n "${schema}" ] && sp="SET search_path = \"${schema}\", pg_catalog;\n" 

echo ${sp} >> "${schemafn}"

awk '($0 !~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${schemafn}"

echo "\nEND;" >> "${schemafn}"

# Foreign keys file
echo "BEGIN;\n" > "${fkfn}"
echo ${sp} >> "${fkfn}"

awk '($0 ~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${fkfn}"

echo "\nEND;" >> "${fkfn}"

# Data file
echo "BEGIN;\n" > "${datafn}"
echo "SET CONSTRAINTS ALL DEFERRED;\n" >> "${datafn}"

mdb-tables -1 "${mdbfn}" | while read TT
do
    mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' "${mdbfn}" "$TT" > "${tf}"

    awk -v pre="${pre}" -v TT="${TT}" \
	'(NR==1) {gsub(/\t/,"\",\""); print "COPY " pre "\"" TT "\"(\"" $0 "\") FROM stdin;";}' "${tf}" >> "${datafn}"
    awk '(NR>1) {gsub(/\t\t/,"\t\\N\t"); gsub(/\t$/,"\t\\N"); gsub(/\t\t/,"\t\\N\t"); print;}' "${tf}" >> "${datafn}"

    echo "\\.\n" >> "${datafn}"
done

echo "END;" >> "${datafn}"

rm -f "${tf}"

If this script is saved to the file access2psql.sh and made executable, then it would be used as follows: 

access2psql.sh file.mdb schema.sql foreignkeys.sql data.sql pg_schema_name
psql -f schema.sql pg_db_name
psql -f data.sql pg_db_name
psql -f foreignkeys.sql pg_db_name

This script won't work properly if there are tab characters in text columns, though the call to mdb-export could be modified to export INSERT statements to fix this. Also, mdb-schema has trouble representing multi-column foreign keys, so foreignkeys.sql may need some manual editing.

Microsoft SQL Server

MySQL

Scripts, programs

Ispirer Toolkit automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from MySQL to PostgreSQL. A free demo of Ispirer Toolkit is available.

pg_chameleon is a python procedure which replicates MySQL into PostgreSQL. The tool can detach the replica for minimal downtime migrations.

ESF Database Migration Toolkit How to migrate MySQL to PostgreSQL in an easy way.

pgloader supports full migration of the database schema and data from a single command line and provides a command language for more complex or specific scenarios. It is still fully supported: please report any bugs on its GitHub page. It appears in the 2013 section here because that's when it has been published first.


2015

2014

2013

Previously

 # To install mysql2psql (under ubuntu 11.10): No need to get from github, just:
 sudo apt-get install ruby gems libmysqlclient-dev libpq-dev
 gem install mysql pg mysql2psql
 # To get info about the mysql socket:
 netstat -l | grep mysql
 mysql2psql # creates a .yml templae
 vi mysql2psql.yml # edit the template
 mysql2psql # connects to mysql database and write into postgres database

Documentation

Oracle

Utilities, tools, scripts etc.

  • Ispirer Toolkit automatically migrates the entire database schema (tables, views, packages, stored procedures, functions, triggers, etc.) and transfers data from Oracle to PostgreSQL. A free demo of Ispirer Toolkit is available.
  • Convertum.ru is a robust tool for automatic migration of more than 25 databases, including migration from Oracle to PostgreSQL. 30-days trial is available for free.
  • Omni Loader is a massively parallel and horizontally scalable enterprise-class database migration solution. Easily loads millions of records per second from Oracle and Exadata into PostgreSQL on-prem or on the cloud. Fully supports Oracle CDC for continuous replication.
  • CYBERTEC Migrator Enterprise-grade Oracle migration software. GUI driven, high performance, low downtime.
  • Full Convert is a reliable and easy-to-use database migration and synchronization solution, converting from more than 40 database engines to PostgreSQL and back (including Oracle).
  • Database migration for Oracle and PostgreSQL Software for cross - database conversion and sync between Oracle and PostgreSQL.
  • Ora2Pg - Oracle to PostgreSQL database schema converter by Gilles Darold
  • PostgreSQL Foreign Data Wrapper for Oracle (oracle_fdw) - an FDW providing support to access Oracle databases from within PostgreSQL
  • ora_migrator Oracle migration toolkit that uses the Oracle foreign data wrapper
  • Orafce - implements common Oracle functions in PostgreSQL for compatibility
  • Oracle-to-PostgreSQL - a program to migrate Oracle databases to PostgreSQL server. The program has high performance due to direct connection to data source and destination databases (it does not use ODBC or any other middleware software). Command line support allows to script, automate and schedule the conversion process.
  • AWS Schema Conversion Tool - to convert your existing database schema from one database engine to another (free download).
  • Exportizer Enterprise Converts data from Oracle to PostgreSQL and back.
  • Splendid Data - Cortex
Cortex is the most advanced product on the market for automated migrations of Oracle databases to native PostgreSQL. This prevents vendor lock-in and delivers maximum freedom of deployment on-premise and/or in the Cloud. Because Cortex establishes all necessary dependencies between Data and Code objects and also migrates an average of 80% of the Code objects automatically, it is unique.

Documentation, articles, presentations etc.

PostgreSQL documentation

Articles and presentations (in reverse chronological order)

Progress RDBMS

Utilities, tools, scripts etc.

  • Ispirer Toolkit automatically migrates the entire database schema and transfers data from Progress to PostgreSQL. A free demo of Ispirer Toolkit is available.

Converting PostgreSQL Databases to other Databases

  • Ispirer Toolkit performs automatic migration of PostgreSQL to any popular RDBMS. A free demo of Ispirer Toolkit is available.
  • Convertum.ru is a robust tool for automatic migration of more than 25 databases, including migration from PostgreSQL to other popular databases. 30-days trial is available for free.
  • Omni Loader is a massively parallel and horizontally scalable enterprise-class database migration solution. It enables rapid migration between 25 databases, on-prem or on the cloud, including data warehouses.
  • PostgreSQL to InterBase dump file converter by Dobrica Pavlinusic
  • dbForge Studio for PostgreSQL helps to migrate data to various relational DB servers such as Oracle, MySQL, SQL Server, MariaDB.
  • Full Convert is a reliable and easy-to-use database migration and synchronization solution, converting from more than 40 database engines to PostgreSQL and back
  • OmniDB Open source full-featured web tool for database management. Currently supports PostgreSQL only. More RDBMS support coming soon, including the ability of converting databases from any supported RDBMS to PostgreSQL and back.
  • Exportizer Enterprise Supports data conversion from PostgreSQL to Oracle, SQL Server, SQLite, Interbase, Firebird, MySQL, DB2, Informix, SQL Anywhere, Microsoft Access, Microsoft Excel, dBase, Paradox, CSV.
  • ESF Database Migration Toolkit A tookit migrate data between various database formats(include PostgreSQL).
Retrieved from "https://wiki.postgresql.org/index.php?title=Converting_from_other_Databases_to_PostgreSQL&oldid=39873"